﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Text;
using DataModel;

namespace BasicDataManagement
{
    public class OpFromDB:IOpertation
    {
        private OpFromDB() { }
        private static OpFromDB instance;
        private static DbDataClassesDataContext db;

        private static OpFromDB Instance
        {
            get
            {
                if (instance == null)
                {
                    XElement xmlfile = XElement.Load("Configuration.xml");
                    string serverip = xmlfile.Element("DatabaseIP").Value;
                    instance = new OpFromDB();
                    db = new DbDataClassesDataContext("Server="+serverip+";database=ZWXDB;User ID=zwx;Password=zwxzwx");
                    return instance;
                }
                return instance;
            }
        }

        public static OpFromDB GetInstance()
        {
            if (instance == null)
            {
                instance = new OpFromDB();
                XElement xmlfile = XElement.Load("Configuration.xml");
                string serverip = xmlfile.Element("DatabaseIP").Value;
                db = new DbDataClassesDataContext("Server=" + serverip + ";database=ZWXDB;Persist Security Info=True;User ID=zwx;Password=zwxzwx");
                return instance;
            }
            return instance;
        }

        #region 插入操作
        /// <summary>
        /// 插入分类
        /// </summary>
        /// <param name="type">物品类型</param>
        /// <param name="catName">分类名称</param>
        public void CreateCategory(ObjectType type, string catName)
        {
            switch (type)
            {
                case ObjectType.Material:
                    {
                        var insertObj = new tblMaterialCategory() { Name = catName };
                        db.tblMaterialCategory.InsertOnSubmit(insertObj);
                        db.SubmitChanges();
                        break;
                    }
                case ObjectType.Option:
                    {
                        var insertObj = new tblOptionCategory() { Name = catName };
                        db.tblOptionCategory.InsertOnSubmit(insertObj);
                        db.SubmitChanges(); 
                        break;
                    }
                case ObjectType.FixedDish:
                    {
                        var insertObj = new tblFixedDishCategory() { Name = catName };
                        db.tblFixedDishCategory.InsertOnSubmit(insertObj);
                        db.SubmitChanges();
                        break;
                    }
                case ObjectType.Combo: 
                    {
                        var insertObj = new tblComboCategory() { Name = catName };
                        db.tblComboCategory.InsertOnSubmit(insertObj);
                        db.SubmitChanges();
                        break; 
                    }
                case ObjectType.ReplaceableDish: 
                    {
                        var insertObj = new tblReplaceableDishCategory() { Name = catName };
                        db.tblReplaceableDishCategory.InsertOnSubmit(insertObj);
                        db.SubmitChanges();
                        break; 
                    }
                default: break;
            }
        }
        public void CreateObject(ObjectType type, object newObj)
        {
            switch (type)
            {
                #region 插入原料
                case ObjectType.Material:
                    {
                        MaterialObject mo = newObj as MaterialObject;
                        var newMaterial = new tblMaterial()
                        {
                            Name = mo.ObjectName,
                            Unit = mo.Unit,
                            BelongedCategoryID = ((from c in db.tblMaterialCategory
                                                    where c.Name == mo.BelongedCategoryName
                                                    select c).First().ID)
                        };
                        db.tblMaterial.InsertOnSubmit(newMaterial);
                        db.SubmitChanges();
                        break;
                    }
                #endregion
                #region 插入自选菜
                case ObjectType.Option: 
                    {
                        OptionObject oo = newObj as OptionObject;
                        var newOpt = new tblOption()
                        {
                            Name = oo.ObjectName,
                            Unit = oo.Unit,
                            BelongedCategoryID = ((from c in db.tblOptionCategory
                                                   where c.Name == oo.BelongedCategoryName
                                                   select c).First().ID)
                        };
                        db.tblOption.InsertOnSubmit(newOpt);
                        db.SubmitChanges();

                        var matList = oo.IngredientList;
                        foreach (var mat in matList)
                        {
                            tblMaterialOfOption newOptMat = new tblMaterialOfOption()
                            {
                                Dosage = mat.Dosage,
                                OptionID = newOpt.ID,
                                MaterialID = ((from c in db.tblMaterial
                                               where c.Name == mat.IngredientName
                                               select c).First().ID)
                            };
                            db.tblMaterialOfOption.InsertOnSubmit(newOptMat);
                            db.SubmitChanges();
                        }
                        break; 
                    }
                #endregion
                #region 插入单品
                case ObjectType.FixedDish:
                    {
                        FixedDishObject fdo = newObj as FixedDishObject;
                        var newFD = new tblFixedDish()
                        {
                             Name = fdo.ObjectName,
                             Unit = fdo.Unit,
                             Price = fdo.Price,
                             BelongedCategoryID = ((from c in db.tblFixedDishCategory
                                                    where c.Name == fdo.BelongedCategoryName
                                                    select c).First().ID)
                        };
                        db.tblFixedDish.InsertOnSubmit(newFD);
                        db.SubmitChanges();

                        var matList = fdo.IngredientList;
                        foreach (var mat in matList)
                        {
                            tblMaterialOfFixedDish newFdMat = new tblMaterialOfFixedDish()
                            {
                                 Dosage = mat.Dosage,
                                 FixedDishID = newFD.ID,
                                 MaterialID = ((from c in db.tblMaterial
                                                where c.Name == mat.IngredientName
                                                select c).First().ID)
                            };
                            db.tblMaterialOfFixedDish.InsertOnSubmit(newFdMat);
                            db.SubmitChanges();
                        }
                        break;
                    }
                #endregion
                #region 插入套餐
                case ObjectType.Combo:
                    {
                        ComboObject co = newObj as ComboObject;
                        var newCombo = new tblCombo()
                        {
                             Name=co.ObjectName,
                             Unit = co.Unit,
                             PriceType = (int)co.PriceType,
                             Price = co.Price,
                             BelongedCategoryID=((from c in db.tblComboCategory
                                                  where c.Name == co.BelongedCategoryName
                                                  select c).First().ID)
                        };
                        db.tblCombo.InsertOnSubmit(newCombo);
                        db.SubmitChanges();

                        var compList = co.ComponentList;
                        foreach (var comp in compList)
                        {
                            var cctable = new tblComboComponent()
                            {
                                Name = comp.ComponentName,
                                BelongedComboID = ((from c in db.tblCombo
                                                    where c.Name == newCombo.Name
                                                    select c).First().ID)
                            };
                            db.tblComboComponent.InsertOnSubmit(cctable);
                            db.SubmitChanges();

                            var ingreList = comp.IngredientList;
                            foreach (var ingre in ingreList)
                            {
                                var tmpFD = new tblFixedDishOfComboComponent()
                                {
                                    ComboComponentID = cctable.ID,
                                    Dosage = ingre.Dosage,
                                    FixedDishID = ((from c in db.tblFixedDish
                                                    where c.Name == ingre.IngredientName
                                                    select c).First().ID)
                                };
                                db.tblFixedDishOfComboComponent.InsertOnSubmit(tmpFD);
                                db.SubmitChanges();
                            }
                        }

                        break;
                    }
                #endregion
                #region 插入自选菜
                case ObjectType.ReplaceableDish:
                    {
                        ReplaceableDishObject rdo = newObj as ReplaceableDishObject;
                        var newRD = new tblReplaceableDish()
                        {
                            Name = rdo.ObjectName,
                            Unit = rdo.Unit,
                            Price = rdo.Price,
                            BelongedCategoryID = ((from c in db.tblReplaceableDishCategory
                                                   where c.Name == rdo.BelongedCategoryName
                                                   select c).First().ID)
                        };
                        db.tblReplaceableDish.InsertOnSubmit(newRD);
                        db.SubmitChanges();

                        var matList = rdo.MaterialList;
                        foreach (var mat in matList)
                        {
                            tblMaterialOfReplaceableDish newRdMat = new tblMaterialOfReplaceableDish()
                            {
                                Dosage = mat.Dosage,
                                ReplaceableDishID = newRD.ID,
                                MaterialID = ((from c in db.tblMaterial
                                               where c.Name == mat.IngredientName
                                               select c).First().ID)
                            };
                            db.tblMaterialOfReplaceableDish.InsertOnSubmit(newRdMat);
                            db.SubmitChanges();
                        }

                        var optList = rdo.OptionList;
                        foreach (var opt in optList)
                        {
                            tblOptionOfReplaceableDish newRdOpt = new tblOptionOfReplaceableDish()
                            {
                                Dosage = opt.Dosage,
                                ReplaceableDishID = newRD.ID,
                                OptionID = ((from c in db.tblOption
                                             where c.Name == opt.IngredientName
                                             select c).First().ID)
                            };
                            db.tblOptionOfReplaceableDish.InsertOnSubmit(newRdOpt);
                            db.SubmitChanges();
                        }

                        break;
                    }
                #endregion
                default: break;
            }
        }
        #endregion

        #region 查询操作

        public List<string> LoadMaterialCategory()
        {
            var materialCatList = from materialCat in db.tblMaterialCategory
                               select materialCat.Name;
            return materialCatList.ToList();
        }

        public List<string> LoadOptionCategory()
        {
            var optionCatList = from optionCat in db.tblOptionCategory
                                select optionCat.Name;
            return optionCatList.ToList();
        }

        public List<string> LoadComboCategory()
        {
            var comboCatList = from comboCat in db.tblComboCategory
                               select comboCat.Name;
            return comboCatList.ToList();
        }

        public List<string> LoadFixedDishCategory()
        {
            var fdCatList = from fdCat in db.tblFixedDishCategory
                            select fdCat.Name;
            return fdCatList.ToList();
        }

        public List<string> LoadReplaceableDishCategory()
        {
            var rdCatList = from rdCat in db.tblReplaceableDishCategory
                            select rdCat.Name;
            return rdCatList.ToList();
        }

        public List<string> LoadMateriaclItemName()
        {
            var matList = from material in db.tblMaterial
                          select material.Name;
            return matList.ToList();
        }

        public List<string> LoadMateriaclItemName(string categoryName)
        {
            var matlist = from mat in db.tblMaterial
                          from cat in db.tblMaterialCategory
                          where cat.Name == categoryName && mat.BelongedCategoryID == cat.ID
                          select mat.Name;
            return matlist.ToList();
        }

        public List<string> LoadOptionItemName()
        {
            var optList = from opt in db.tblOption
                          select opt.Name;
            return optList.ToList();
        }

        public List<string> LoadOptionItemName(string categoryName)
        {
            var optList = from opt in db.tblOption
                          from cat in db.tblOptionCategory
                          where cat.Name == categoryName && opt.BelongedCategoryID == cat.ID
                          select opt.Name;
            return optList.ToList();
        }

        public List<string> LoadComboItemName()
        {
            var comboList = from combo in db.tblCombo
                            select combo.Name;
            return comboList.ToList();
        }

        public List<string> LoadComboItemName(string categoryName)
        {
            var comboList = from combo in db.tblCombo
                            from cat in db.tblComboCategory
                            where cat.Name == categoryName && combo.BelongedCategoryID == cat.ID
                            select combo.Name;
            return comboList.ToList();
        }

        public List<string> LoadFixedDishItemName()
        {
            var fdList = from fd in db.tblFixedDish
                         select fd.Name;
            return fdList.ToList();
        }

        public List<string> LoadFixedDishItemName(string categoryName)
        {
            var fdList = from fd in db.tblFixedDish
                         from cat in db.tblFixedDishCategory
                         where cat.Name == categoryName && fd.BelongedCategoryID == cat.ID
                         select fd.Name;
            return fdList.ToList();
        }

        public List<string> LoadReplaceableDishItemName()
        {
            var rdList = from rd in db.tblReplaceableDish
                         select rd.Name;
            return rdList.ToList();
        }

        public List<string> LoadReplaceableDishItemName(string categoryName)
        {
            var rdList = from rd in db.tblReplaceableDish
                         from cat in db.tblReplaceableDishCategory
                         where cat.Name == categoryName && rd.BelongedCategoryID == cat.ID
                         select rd.Name;
            return rdList.ToList();
        }

        public List<string> LoadReplaceableDishItemOptionNames(string rDishName)
        {
            var result = from c in db.tblReplaceableDish
                         from d in db.tblOptionOfReplaceableDish
                         from e in db.tblOption
                         where c.Name == rDishName && c.ID == d.ReplaceableDishID && e.ID == d.OptionID
                         select e.Name;
            return result.ToList();
                           
        }

        public List<string> LoadComboItemComponentNames(string comboName)
        {
            var result = from c in db.tblCombo
                         from d in db.tblComboComponent
                         where c.ID == d.BelongedComboID && c.Name == comboName
                         select d.Name;
            return result.ToList();
        }

        public List<string> LoadComboItemComponentIngredientNames(string comboName, string componentName)
        {
            var result = from c in db.tblCombo
                         from d in db.tblComboComponent
                         from e in db.tblFixedDishOfComboComponent
                         from f in db.tblFixedDish
                         where c.Name == comboName && d.BelongedComboID == c.ID && d.Name == componentName && e.ComboComponentID == d.ID && e.FixedDishID == f.ID
                         select f.Name;
            return result.ToList();
        }

        public List<DataModel.MaterialObject> LoadMaterialObjects()
        {
            var result = from c in db.tblMaterial
                         select new MaterialObject()
                         {
                             ObjectName = c.Name,
                             Unit = c.Unit,
                             CheckProperty = c.CheckID,
                             Stock=(float)c.Stock,
                             ID=c.ID,
                             BelongedCategoryName = (from d in db.tblMaterialCategory
                                                     where d.ID == c.BelongedCategoryID
                                                     select d).First().Name
                         };
            return result.ToList();
            //未测试
        }

        public List<DataModel.MaterialObject> LoadMaterialObjects(string categoryName)
        {
            var result = from c in db.tblMaterial
                         from d in db.tblMaterialCategory
                         where d.Name == categoryName && c.BelongedCategoryID == d.ID
                         select new MaterialObject()
                         {
                             ObjectName=c.Name,
                             Unit=c.Unit,
                             Stock = (float)c.Stock,
                             ID = c.ID,
                             BelongedCategoryName = categoryName,
                             CheckProperty=c.CheckID
                         };
            return result.ToList();
        }

        public List<DataModel.FixedDishObject> LoadFixedDishObjects()
        {
            var result = from fd in db.tblFixedDish
                         from fdc in db.tblFixedDishCategory
                         where fd.BelongedCategoryID == fdc.ID
                         select new FixedDishObject()
                         {
                             ObjectName = fd.Name,
                             Unit = fd.Unit,
                             BelongedCategoryName = fdc.Name,
                             Price = fd.Price,
                             ID=fd.ID,
                             IngredientList = (from mat in db.tblMaterial
                                               from rel in db.tblMaterialOfFixedDish
                                               where rel.FixedDishID == fd.ID && rel.MaterialID == mat.ID
                                               select new Ingredient()
                                               {
                                                   Dosage = rel.Dosage,
                                                   IngredientName = mat.Name,
                                                   Unit = mat.Unit
                                               }).ToList()

                         };
            return result.ToList();
        }

        public List<DataModel.FixedDishObject> LoadFixedDishObjects(string categoryName)
        {
            var source = LoadFixedDishObjects();
            var result = from c in source
                         where c.BelongedCategoryName == categoryName
                         select c;
            return result.ToList();
        }

        public List<DataModel.ComboObject> LoadComboObjects()
        {
            var result = from combo in db.tblCombo
                         from cat in db.tblComboCategory
                         where combo.BelongedCategoryID == cat.ID
                         select new ComboObject()
                         {
                             ObjectName = combo.Name,
                             Unit = combo.Unit,
                             BelongedCategoryName = cat.Name,
                             PriceType = (PriceType)combo.PriceType,
                             Price = combo.Price,
                             ComponentList = (from comp in db.tblComboComponent
                                              where comp.BelongedComboID == combo.ID
                                              select new ComboComponentObject()
                                              {
                                                  ComponentName = comp.Name,
                                                  IngredientList = (from ingreRel in db.tblFixedDishOfComboComponent
                                                                    from fd in db.tblFixedDish
                                                                    where ingreRel.ComboComponentID == comp.ID && ingreRel.FixedDishID == fd.ID
                                                                    select new Ingredient()
                                                                    {
                                                                        IngredientName = fd.Name,
                                                                        Unit = fd.Unit,
                                                                        Dosage = ingreRel.Dosage
                                                                    }
                                                  ).ToList()
                                              }
                                                 ).ToList()
                         };
            return result.ToList();
        }

        public List<DataModel.ComboObject> LoadComboObjects(string categoryName)
        {
            var source = LoadComboObjects();
            var result = from c in source
                         where c.BelongedCategoryName == categoryName
                         select c;
            return result.ToList();
        }

        public List<DataModel.OptionObject> LoadOptionObjects()
        {
            var result = from opt in db.tblOption
                         from cat in db.tblOptionCategory
                         where opt.BelongedCategoryID == cat.ID
                         select new OptionObject()
                         {
                             ObjectName = opt.Name,
                             Unit = opt.Unit,
                             ID=opt.ID,
                             BelongedCategoryName = cat.Name,
                             IngredientList = (from mat in db.tblMaterial
                                               from rel in db.tblMaterialOfOption
                                               where rel.MaterialID == mat.ID && rel.OptionID == opt.ID
                                               select new Ingredient()
                                               {
                                                   IngredientName = mat.Name,
                                                   Unit = mat.Unit,
                                                   Dosage = rel.Dosage
                                               }
                             ).ToList()
                         };
            return result.ToList();
            //未测试
        }

        public List<DataModel.OptionObject> LoadOptionObjects(string categoryName)
        {
            var source = LoadOptionObjects();
            var result = from c in source
                         where c.BelongedCategoryName == categoryName
                         select c;
            return result.ToList();
            //未测试 
        }

        public List<DataModel.ReplaceableDishObject> LoadReplaceableDishObjects()
        {
            var result = from rd in db.tblReplaceableDish
                         from cat in db.tblReplaceableDishCategory
                         where rd.BelongedCategoryID == cat.ID
                         select new ReplaceableDishObject()
                         {
                             ObjectName = rd.Name,
                             Unit = rd.Unit,
                             BelongedCategoryName = cat.Name,
                             Price = rd.Price,
                             OptionList = (from opt in db.tblOption
                                           from rel in db.tblOptionOfReplaceableDish
                                           where rel.OptionID == opt.ID && rel.ReplaceableDishID == rd.ID
                                           select new Ingredient()
                                           {
                                               IngredientName = opt.Name,
                                               Unit = opt.Unit,
                                               Dosage = rel.Dosage
                                           }
                             ).ToList(),
                             MaterialList = (from mat in db.tblMaterial
                                             from rel in db.tblMaterialOfReplaceableDish
                                             where rel.MaterialID == mat.ID && rel.ReplaceableDishID == rd.ID
                                             select new Ingredient()
                                             {
                                                 IngredientName = mat.Name,
                                                 Unit = mat.Unit,
                                                 Dosage = rel.Dosage
                                             }
                             ).ToList()
                         };
            return result.ToList();
            //未测试
        }

        public List<DataModel.ReplaceableDishObject> LoadReplaceableDishObjects(string categoryName)
        {
            var source = LoadReplaceableDishObjects();
            var result = from c in source
                         where c.BelongedCategoryName == categoryName
                         select c;
            return result.ToList();
        }

        public List<DataModel.IngredientSimpleObject> LoadMaterialSimpleObjects()
        {
            return (from c in db.tblMaterial
                    from cat in db.tblMaterialCategory
                    where c.BelongedCategoryID == cat.ID
                    select new IngredientSimpleObject()
                    {
                        ObjectName = c.Name,
                        BelongedCategory = cat.Name,
                        Unit = c.Unit
                    }).ToList();
        }

        public List<DataModel.IngredientSimpleObject> LoadOptionSimpleObjects()
        {
            return (from c in db.tblOption
                    from cat in db.tblOptionCategory
                    where c.BelongedCategoryID == cat.ID
                    select new IngredientSimpleObject()
                    {
                        ObjectName = c.Name,
                        BelongedCategory = cat.Name,
                        Unit = c.Unit
                    }).ToList();
        }

        public List<DataModel.IngredientSimpleObject> LoadFixedDishSimpleObjects()
        {
            return (from c in db.tblFixedDish
                    from cat in db.tblFixedDishCategory
                    where c.BelongedCategoryID == cat.ID
                    select new IngredientSimpleObject()
                    {
                        ObjectName = c.Name,
                        BelongedCategory = cat.Name,
                        Unit = c.Unit
                    }).ToList();
        }

        public List<DataModel.IngredientSimpleObject> LoadReplaceableDishSimpleObjects()
        {
            return (from c in db.tblReplaceableDish
                    from cat in db.tblReplaceableDishCategory
                    where c.BelongedCategoryID == cat.ID
                    select new IngredientSimpleObject()
                    {
                        ObjectName = c.Name,
                        BelongedCategory = cat.Name,
                        Unit = c.Unit
                    }).ToList();
        }

        public List<DataModel.IngredientSimpleObject> LoadComboSimpleObjects()
        {
            return (from c in db.tblCombo
                    from cat in db.tblComboCategory
                    where c.BelongedCategoryID == cat.ID
                    select new IngredientSimpleObject()
                    {
                        ObjectName = c.Name,
                        BelongedCategory = cat.Name,
                        Unit = c.Unit
                    }).ToList();
        }

        #endregion

        public int AddBill(List<BillItem> billItemList, int logid, decimal discount, string eatPlace, string cashierIP)
        {
            var bill = new tblBill() { BillTime = DateTime.Now, LogID = logid, Discount = discount, EatPlace = eatPlace, CashierIP = cashierIP };
            db.tblBill.InsertOnSubmit(bill);
            db.SubmitChanges();

            foreach (var t in billItemList)
            {
                var billitem = new tblBillItem() { BillID = bill.ID, ItemType = (int)t.dishType, Price=t.Price };
                db.tblBillItem.InsertOnSubmit(billitem);
                db.SubmitChanges();

                switch (t.dishType)
                {
                    #region 添加单品项
                    case DishType.FixedDish:
                        {
                            var fditembill = new tblFixedDishBill()
                            {
                                BillItemID = billitem.ID,
                                FixedDishID = (from c in db.tblFixedDish
                                               where c.Name == t.dishName
                                               select c).First().ID,
                            };
                            db.tblFixedDishBill.InsertOnSubmit(fditembill);
                            db.SubmitChanges();
                            //计入库存
                            var matsOfFd = from mat in db.tblMaterial
                                           from matOffd in db.tblMaterialOfFixedDish
                                           where matOffd.FixedDishID == fditembill.FixedDishID && matOffd.MaterialID == mat.ID
                                           select new { matID = mat.ID, matName = mat.Name, usage = matOffd.Dosage };
                            foreach (var tmpMatOfFd in matsOfFd)
                            {
                                StockRecordItem tmpStockRecItem = new StockRecordItem()
                                {
                                    MaterialName = tmpMatOfFd.matName,
                                    MaterialID = tmpMatOfFd.matID,
                                    StockDir = StockDirection.TakeOut,
                                    RecTime = bill.BillTime,
                                    Value = tmpMatOfFd.usage,
                                    Reason = t.dishName + " 单品原料用料"
                                };
                                AddRec(tmpStockRecItem);
                            }
                            break;
                        }
                    #endregion
                    #region 添加自选菜项
                    case DishType.Replaceable:
                        {
                            var rditembill = new tblReplaceableDishBill()
                            {
                                BillItemID = billitem.ID,
                                ReplaceableDishID = (from c in db.tblReplaceableDish
                                                     where c.Name == t.dishName
                                                     select c).First().ID,
                            };
                            db.tblReplaceableDishBill.InsertOnSubmit(rditembill);
                            db.SubmitChanges();
                            //原料使用计入库存
                            var matsOfRd = from mat in db.tblMaterial
                                           from matOfrd in db.tblMaterialOfReplaceableDish
                                           where matOfrd.ReplaceableDishID == rditembill.ReplaceableDishID && matOfrd.MaterialID == mat.ID
                                           select new { matID = mat.ID, matName = mat.Name, usage = matOfrd.Dosage };
                            foreach (var tmpMatOfRd in matsOfRd)
                            {
                                StockRecordItem tmpStockRecItem = new StockRecordItem()
                                {
                                    MaterialID = tmpMatOfRd.matID,
                                    MaterialName = tmpMatOfRd.matName,
                                    StockDir = StockDirection.TakeOut,
                                    RecTime = bill.BillTime,
                                    Value = tmpMatOfRd.usage,
                                    Reason = t.dishName + " 自选菜原料用料"
                                };
                                AddRec(tmpStockRecItem);
                            }

                            foreach (var tmp in t.options)
                            {
                                var optbill = new tblReplaceableDishOptionBill()
                                {
                                    ReplaceableDishBillID = rditembill.ID,
                                    Dosage = 0.0,//需要改
                                    OptionID = (from c in db.tblOption
                                                where c.Name == tmp
                                                select c).First().ID
                                };
                                db.tblReplaceableDishOptionBill.InsertOnSubmit(optbill);
                                db.SubmitChanges();
                                //配菜原料使用计入库存
                                var matsOfOpt = from mat in db.tblMaterial
                                                from matOfopt in db.tblMaterialOfOption
                                                from opt in db.tblOptionOfReplaceableDish
                                                where matOfopt.OptionID == optbill.OptionID && matOfopt.MaterialID == mat.ID && opt.OptionID == optbill.OptionID
                                                && opt.ReplaceableDishID == rditembill.ReplaceableDishID
                                                select new { matID = mat.ID, matName = mat.Name, usage = matOfopt.Dosage * opt.Dosage / t.options.Count };
                                foreach (var tmpMatOfOpt in matsOfOpt)
                                {
                                    StockRecordItem tmpStockRecItem = new StockRecordItem()
                                    {
                                        MaterialID = tmpMatOfOpt.matID,
                                        MaterialName = tmpMatOfOpt.matName,
                                        StockDir = StockDirection.TakeOut,
                                        Value = tmpMatOfOpt.usage,
                                        RecTime = bill.BillTime,
                                        Reason = t.dishName + " - " + tmp + " 配菜原料用料"
                                    };
                                    AddRec(tmpStockRecItem);
                                }
                            }

                            break;
                        }
                    #endregion
                    #region 添加套餐项
                    case DishType.Combo:
                        {
                            var combobill = new tblComboBill()
                            {
                                BillItemID = billitem.ID,
                                ComboID = (from c in db.tblCombo
                                           where c.Name == t.dishName
                                           select c).First().ID,
                            };
                            db.tblComboBill.InsertOnSubmit(combobill);
                            db.SubmitChanges();

                            foreach (var tmp in t.comboDishes)
                            {
                                var combodishbill = new tblComboFixedDishBill()
                                {
                                    ComboBillID = combobill.ID,
                                    FixedDishID = (from c in db.tblFixedDish
                                                   where c.Name == tmp
                                                   select c).First().ID,
                                };
                                db.tblComboFixedDishBill.InsertOnSubmit(combodishbill);
                                db.SubmitChanges();

                                //套餐原料使用计入库存
                                var matsOfFd = from mat in db.tblMaterial
                                               from matOffd in db.tblMaterialOfFixedDish
                                               where matOffd.FixedDishID==combodishbill.FixedDishID && matOffd.MaterialID == mat.ID 
                                               select new { matID = mat.ID, matName = mat.Name, usage = matOffd.Dosage };
                                foreach (var tmpMatOfFd in matsOfFd)
                                {
                                    StockRecordItem tmpStockRecItem = new StockRecordItem()
                                    {
                                        MaterialName = tmpMatOfFd.matName,
                                        MaterialID = tmpMatOfFd.matID,
                                        StockDir = StockDirection.TakeOut,
                                        RecTime = bill.BillTime,
                                        Value = tmpMatOfFd.usage,
                                        Reason = t.dishName+" - "+tmp + " 套餐原料用料"
                                    };
                                    AddRec(tmpStockRecItem);
                                }
                            }

                            break;
                        }
                    #endregion
                    default:
                        {
                            throw new Exception("账单插入错误，非法类型");
                        }
                }
            }
            return bill.ID;
        }
        //获取本单的实收价
        public decimal LoadBillPrice(int billid)
        {
            var prices = (from c in db.tblBillItem
                       where c.BillID == billid
                       select c.Price);
            if (prices.Count() == 0)
            {
                return 0.0m;
            }
            var sum = prices.Sum();
            var discount = ((from c in db.tblBill
                             where c.ID == billid
                             select c.Discount).First());
            System.Diagnostics.Debug.WriteLine(sum, "Sum:");
            System.Diagnostics.Debug.WriteLine(discount, "Discount:");
            return sum-discount;
        }

        public decimal LoadStaffLogPrice(int logid)
        {
            var result = (from bill in db.tblBill
                          from item in db.tblBillItem
                          where bill.LogID == logid && item.BillID == bill.ID
                          select item.Price);
            if (result.Count() > 0)
            {
                return result.Sum();
            }
            else
            {
                return 0.00M;
            }
            //return result;
        }

        public int LoadStaffLogDealCount(int logid)
        {
            return (from bill in db.tblBill
                    where bill.LogID == logid
                    select bill).Count();
        }

        private void AddRec(StockRecordItem sri)
        {
            var recItem = new tblMaterialStockRecord()
            {
                Direction = (int)sri.StockDir,
                MaterialID = sri.MaterialID,
                Value = sri.Value,
                RecordTime = sri.RecTime,
                Reason = sri.Reason
            };
            db.tblMaterialStockRecord.InsertOnSubmit(recItem);
            db.SubmitChanges();
        }

        public List<StockRecordItem> LoadMaterialStockRecordsAll()
        {
            return (from mat in db.tblMaterial
                    from rec in db.tblMaterialStockRecord
                    where mat.ID == rec.MaterialID
                    select new StockRecordItem()
                    {
                        MaterialName = mat.Name,
                        RecTime = rec.RecordTime,
                        StockDir = (StockDirection)rec.Direction,
                        Value = rec.Value,
                        Reason = rec.Reason
                    }).ToList();
        }

        public List<StockRecordItem> LoadMaterialStockRecordsCategory(string categoryName)
        {
            return (from mat in db.tblMaterial
                    from cat in db.tblMaterialCategory
                    from rec in db.tblMaterialStockRecord
                    where cat.Name==categoryName && cat.ID==mat.BelongedCategoryID && mat.ID == rec.MaterialID
                    select new StockRecordItem()
                    {
                        MaterialName = mat.Name,
                        RecTime = rec.RecordTime,
                        StockDir = (StockDirection)rec.Direction,
                        Value = rec.Value,
                        Reason = rec.Reason
                    }).ToList();
        }

        public List<StockRecordItem> LoadMaterialStockRecordsMaterial(string materialName)
        {
            return (from mat in db.tblMaterial
                    from rec in db.tblMaterialStockRecord
                    where mat.Name==materialName && mat.ID == rec.MaterialID
                    select new StockRecordItem()
                    {
                        MaterialName = mat.Name,
                        RecTime = rec.RecordTime,
                        StockDir = (StockDirection)rec.Direction,
                        Value = rec.Value,
                        Reason = rec.Reason,
                        CheckType=mat.CheckID
                    }).ToList();
        }

        #region 更新方法 //均未测试

        public void UpdateMaterial(string PreviousName, MaterialObject materialObj)
        {
            var matresult = (from mat in db.tblMaterial
                             where mat.Name == PreviousName
                             select mat).First();
            matresult.Name = materialObj.ObjectName;
            matresult.Unit = materialObj.Unit;
            matresult.BelongedCategoryID = (from cat in db.tblMaterialCategory
                                            where cat.Name == materialObj.BelongedCategoryName
                                            select cat).First().ID;
            db.SubmitChanges();
        }

        public void UpdateOption(string PreviousName, OptionObject optionObj)
        {
            var optionresult = (from opt in db.tblOption
                                where opt.Name == PreviousName
                                select opt).First();
            optionresult.Name = optionObj.ObjectName;
            optionresult.Unit = optionObj.Unit;
            optionresult.BelongedCategoryID = (from cat in db.tblOptionCategory
                                               where cat.Name == optionObj.BelongedCategoryName
                                               select cat).First().ID;
            db.SubmitChanges();
            var matList = from mat in db.tblMaterialOfOption
                          where mat.OptionID == optionresult.ID
                          select mat;
            db.tblMaterialOfOption.DeleteAllOnSubmit(matList);
            db.SubmitChanges();
            foreach (var t in optionObj.IngredientList)
            {
                tblMaterialOfOption tmpMatOfOpt = new tblMaterialOfOption();
                tmpMatOfOpt.OptionID = optionresult.ID;
                tmpMatOfOpt.MaterialID = (from c in db.tblMaterial
                                          where c.Name == t.IngredientName
                                          select c).First().ID;
                tmpMatOfOpt.Dosage = t.Dosage;
                db.tblMaterialOfOption.InsertOnSubmit(tmpMatOfOpt);
            }
            db.SubmitChanges();
        }

        public void UpdateFixedDish(string PreviousName, FixedDishObject fdObj)
        {
            var fdresult = (from fd in db.tblFixedDish
                            where fd.Name == PreviousName
                            select fd).First();
            fdresult.Name = fdObj.ObjectName;
            fdresult.Unit = fdObj.Unit;
            fdresult.Price = fdObj.Price;
            fdresult.BelongedCategoryID = (from cat in db.tblFixedDishCategory
                                           where cat.Name == fdObj.BelongedCategoryName
                                           select cat).First().ID;
            db.SubmitChanges();
            var matList = from mat in db.tblMaterialOfFixedDish
                          where mat.FixedDishID == fdresult.ID
                          select mat;
            db.tblMaterialOfFixedDish.DeleteAllOnSubmit(matList);
            db.SubmitChanges();

            foreach (var t in fdObj.IngredientList)
            {
                tblMaterialOfFixedDish tmpMatOfFd = new tblMaterialOfFixedDish();
                tmpMatOfFd.FixedDishID = fdresult.ID;
                tmpMatOfFd.MaterialID = (from c in db.tblMaterial
                                         where c.Name == t.IngredientName
                                         select c).First().ID;
                tmpMatOfFd.Dosage = t.Dosage;
                db.tblMaterialOfFixedDish.InsertOnSubmit(tmpMatOfFd);
            }
            db.SubmitChanges();
        }

        public void UpdateCombo(string PreviousName, ComboObject comboObj)
        {
            var comboresult = (from combo in db.tblCombo
                               where combo.Name == PreviousName
                               select combo).First();
            comboresult.Name = comboObj.ObjectName;
            comboresult.PriceType = (int)comboObj.PriceType;
            comboresult.Price = comboObj.Price;
            comboresult.Unit = comboObj.Unit;
            comboresult.BelongedCategoryID = (from cat in db.tblComboCategory
                                              where cat.Name == comboObj.BelongedCategoryName
                                              select cat).First().ID;
            db.SubmitChanges();
            var fdList = from comp in db.tblComboComponent
                         from fd in db.tblFixedDishOfComboComponent
                         where comp.BelongedComboID == comboresult.ID && fd.ComboComponentID == comp.ID
                         select fd;
            db.tblFixedDishOfComboComponent.DeleteAllOnSubmit(fdList);
            db.SubmitChanges();

            var compList = from comp in db.tblComboComponent
                           where comp.BelongedComboID == comboresult.ID
                           select comp;
            db.tblComboComponent.DeleteAllOnSubmit(compList);
            db.SubmitChanges();

            foreach (var comp in comboObj.ComponentList)
            {
                var tmpcomp = new tblComboComponent();
                tmpcomp.BelongedComboID = comboresult.ID;
                tmpcomp.Name = comp.ComponentName;
                db.tblComboComponent.InsertOnSubmit(tmpcomp);
                db.SubmitChanges();

                foreach (var fd in comp.IngredientList)
                {
                    var tmpfd = new tblFixedDishOfComboComponent();
                    tmpfd.ComboComponentID = tmpcomp.ID;
                    tmpfd.Dosage = fd.Dosage;
                    tmpfd.FixedDishID = (from c in db.tblFixedDish
                                         where c.Name == fd.IngredientName
                                         select c).First().ID;
                    db.tblFixedDishOfComboComponent.InsertOnSubmit(tmpfd);
                }
                db.SubmitChanges();
            }
        }

        public void UpdateReplaceableDish(string PreviousName, ReplaceableDishObject rdObj)
        {
            var rdresult = (from rd in db.tblReplaceableDish
                            where rd.Name == PreviousName
                            select rd).First();
            rdresult.Name = rdObj.ObjectName;
            rdresult.Price = rdObj.Price;
            rdresult.Unit = rdObj.Unit;
            rdresult.BelongedCategoryID = (from cat in db.tblReplaceableDishCategory
                                           where cat.Name == rdObj.BelongedCategoryName
                                           select cat).First().ID;
            db.SubmitChanges();
            var matList = from mat in db.tblMaterialOfReplaceableDish
                          where mat.ReplaceableDishID == rdresult.ID
                          select mat;
            db.tblMaterialOfReplaceableDish.DeleteAllOnSubmit(matList);
            db.SubmitChanges();

            foreach (var t in rdObj.MaterialList)
            {
                tblMaterialOfReplaceableDish tmpMatOfRd = new tblMaterialOfReplaceableDish();
                tmpMatOfRd.ReplaceableDishID = rdresult.ID;
                tmpMatOfRd.MaterialID = (from c in db.tblMaterial
                                         where c.Name == t.IngredientName
                                         select c).First().ID;
                tmpMatOfRd.Dosage = t.Dosage;
                db.tblMaterialOfReplaceableDish.InsertOnSubmit(tmpMatOfRd);
            }
            db.SubmitChanges();

            var optList = from opt in db.tblOptionOfReplaceableDish
                          where opt.ReplaceableDishID == rdresult.ID
                          select opt;
            db.tblOptionOfReplaceableDish.DeleteAllOnSubmit(optList);
            db.SubmitChanges();
            foreach (var t in rdObj.OptionList)
            {
                tblOptionOfReplaceableDish tmpOptOfRd = new tblOptionOfReplaceableDish();
                tmpOptOfRd.ReplaceableDishID = rdresult.ID;
                tmpOptOfRd.OptionID = (from c in db.tblOption
                                       where c.Name == t.IngredientName
                                       select c).First().ID;
                tmpOptOfRd.Dosage = t.Dosage;
                db.tblOptionOfReplaceableDish.InsertOnSubmit(tmpOptOfRd);
            }
            db.SubmitChanges();
        }

        public void UpdateSubcatName(string catType, string previousName, string newName)
        {
            switch (catType)
            {
                case "单品":
                    {
                        tblFixedDishCategory tmpcat = (from cat in db.tblFixedDishCategory
                                                       where cat.Name == previousName
                                                       select cat).First();
                        tmpcat.Name = newName;
                        db.SubmitChanges();
                        break;
                    }
                case "套餐":
                    {
                        tblComboCategory tmpcat = (from cat in db.tblComboCategory
                                                   where cat.Name == previousName
                                                   select cat).First();
                        tmpcat.Name = newName;
                        db.SubmitChanges();
                        break;
                    }
                case "配菜":
                    {
                        tblOptionCategory tmpcat = (from cat in db.tblOptionCategory
                                                    where cat.Name == previousName
                                                    select cat).First();
                        tmpcat.Name = newName;
                        db.SubmitChanges();
                        break;
                    }
                case "原料":
                    {
                        tblMaterialCategory tmpcat = (from cat in db.tblMaterialCategory
                                                      where cat.Name == previousName
                                                      select cat).First();
                        tmpcat.Name = newName;
                        db.SubmitChanges();
                        break;
                    }
                case "自选菜":
                    {
                        tblReplaceableDishCategory tmpcat = (from cat in db.tblReplaceableDishCategory
                                                             where cat.Name == previousName
                                                             select cat).First();
                        tmpcat.Name = newName;
                        db.SubmitChanges();
                        break;
                    }
                default: break;
            }
        }

        #endregion


        #region IOpertation 成员


        public List<AuthorityGroup> LoadAuthorityGroups()
        {
            var result = from c in db.tblAuthorityGroup
                         select new AuthorityGroup(c.Name)
                         {
                             CashierPermission = c.CashierPermission,
                             DataManagePermission = c.DataManagePermission,
                             MemberManagePermission = c.MemberManagePermission,
                             MoneyManagePermission = c.MoneyManagePermission,
                             ReliefPermission = c.ReliefPermission,
                             StaffManagePermission = c.StaffManagePermission,
                             StockManagePermission = c.StockManagePermission,
                             CashFlowPermission=c.CashierPermission,
                             CheckStockPermission=c.CheckStockPermission,
                             PurchasePermission=c.PurchasePermission,
                             ReimbursementPermission=c.ReimbursementPermission
                         };
            return result.ToList();
        }

        public AuthorityGroup LoadAuthorityGroup(string groupName)
        {
            var source = LoadAuthorityGroups();
            var result = (from c in source
                          where c.GroupName == groupName
                          select c).First();
            return result;
        }

        public List<StaffInfo> LoadStaffs()
        {
            var result = from c in db.tblStaff
                         select new StaffInfo()
                         {
                             GroupName = (from d in db.tblAuthorityGroup
                                          where d.ID == c.GroupID
                                          select d).First().Name,
                             JobNumber = c.JobNumber,
                             LoginPWD = c.LoginPWD,
                             Name = c.Name,
                             Money=c.Money
                         };
            return result.ToList();
        }

        public List<StaffInfo> LoadStaffs(string groupName)
        {
            var source = LoadStaffs();
            var result = from c in source
                         where c.GroupName == groupName
                         select c;
            return result.ToList();
        }

        public void AddAuthorityGroup(AuthorityGroup newGroup)
        {
            var tmpGroup = new tblAuthorityGroup()
            {
                Name = newGroup.GroupName,
                CashierPermission = newGroup.CashierPermission,
                DataManagePermission = newGroup.DataManagePermission,
                MemberManagePermission = newGroup.MemberManagePermission,
                MoneyManagePermission = newGroup.MoneyManagePermission,
                ReliefPermission = newGroup.ReliefPermission,
                StaffManagePermission = newGroup.StaffManagePermission,
                StockManagePermission = newGroup.StockManagePermission,
                CashFlowPermission=newGroup.CashFlowPermission,
                CheckStockPermission = newGroup.CheckStockPermission,
                PurchasePermission = newGroup.PurchasePermission,
                ReimbursementPermission = newGroup.ReimbursementPermission
                
            };
            db.tblAuthorityGroup.InsertOnSubmit(tmpGroup);
            db.SubmitChanges();
        }

        public void AddStaff(StaffInfo staff)
        {
            var tmpStaff = new tblStaff()
            {
                Name = staff.Name,
                JobNumber = staff.JobNumber,
                LoginPWD = staff.LoginPWD,
                GroupID = (from c in db.tblAuthorityGroup
                           where c.Name == staff.GroupName
                           select c).First().ID
            };
            db.tblStaff.InsertOnSubmit(tmpStaff);
            db.SubmitChanges();
        }

        public void UpdateStaff(StaffInfo staff)
        {
            var source = (from c in db.tblStaff
                          where c.JobNumber == staff.JobNumber
                          select c).First();
            source.Name = staff.Name;
            source.LoginPWD = staff.LoginPWD;
            source.GroupID = (from c in db.tblAuthorityGroup
                              where c.Name == staff.GroupName
                              select c).First().ID;
            db.SubmitChanges();

        }

        public void UpdateAuthorityGroup(AuthorityGroup group2)
        {
            var source = (from c in db.tblAuthorityGroup
                          where c.Name == group2.GroupName
                          select c).First();
            source.CashierPermission = group2.CashierPermission;
            source.DataManagePermission = group2.DataManagePermission;
            source.MemberManagePermission = group2.MemberManagePermission;
            source.MoneyManagePermission = group2.MoneyManagePermission;
            source.ReliefPermission = group2.ReliefPermission;
            source.StaffManagePermission = group2.StaffManagePermission;
            source.StockManagePermission = group2.StockManagePermission;
            source.CashFlowPermission = group2.CashFlowPermission;
            source.CheckStockPermission = group2.CheckStockPermission;
            source.PurchasePermission = group2.PurchasePermission;
            source.ReimbursementPermission = group2.ReimbursementPermission;
            db.SubmitChanges();
        }

        public void DeleteAuthorityGroup(AuthorityGroup group2)
        {
            var staffs = LoadStaffs(group2.GroupName);
            foreach (var t in staffs)
            {
                DeleteStaff(t);
            }
            var source = (from c in db.tblAuthorityGroup
                          where c.Name == group2.GroupName
                          select c).First();
            db.tblAuthorityGroup.DeleteOnSubmit(source);
            db.SubmitChanges();
        }

        public void DeleteStaff(StaffInfo staff)
        {
            var source = (from c in db.tblStaff
                          where c.JobNumber == staff.JobNumber
                          select c).First();
            db.tblStaff.DeleteOnSubmit(source);
            db.SubmitChanges();
        }

        public SystemUser Login(string lognum, string pwd, string cashierip)
        {
            try
            {
                var userresult = (from staff in db.tblStaff
                                  where staff.JobNumber == lognum && staff.LoginPWD == pwd
                                  select staff).First();
                var log = new tblLogRecord()
                {
                    LoginTime = DateTime.Now,
                    StaffID = userresult.ID,
                };
                db.tblLogRecord.InsertOnSubmit(log);
                db.SubmitChanges();

                var newlogin = new tblLogoutRecord()
                {
                    CashierIP = cashierip,
                    LoginID = log.ID,
                    StaffJobNum = userresult.JobNumber
                };
                db.tblLogoutRecord.InsertOnSubmit(newlogin);
                db.SubmitChanges();

                return new SystemUser()
                {
                    Group = (from gro in db.tblAuthorityGroup
                             where gro.ID == userresult.GroupID
                             select new AuthorityGroup(gro.Name)
                             {
                                 CashierPermission = gro.CashierPermission,
                                 DataManagePermission = gro.DataManagePermission,
                                 MemberManagePermission = gro.MemberManagePermission,
                                 MoneyManagePermission = gro.MoneyManagePermission,
                                 ReliefPermission = gro.ReliefPermission,
                                 StaffManagePermission = gro.StaffManagePermission,
                                 StockManagePermission = gro.StockManagePermission,
                                 CashFlowPermission=gro.CashFlowPermission,
                                 CheckStockPermission=gro.CheckStockPermission,
                                 PurchasePermission=gro.PurchasePermission,
                                 ReimbursementPermission=gro.ReimbursementPermission
                             }).First(),
                    StaffID = userresult.ID,
                    StaffName = userresult.Name,
                    LogID = log.ID,
                    StaffJobNum = userresult.JobNumber
                };
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public DateTime LoadLoginTime(SystemUser user)
        {
            return (from c in db.tblLogRecord
                    where c.ID == user.LogID
                    select c.LoginTime).First();
        }

        public void Logout(SystemUser user, string cashierip)
        {
            var result = (from c in db.tblLogRecord
                          where c.ID == user.LogID
                          select c).First();
            result.LogoutTime = DateTime.Now;
            db.SubmitChanges();

            var delresult = (from c in db.tblLogoutRecord
                             where c.StaffJobNum == user.StaffJobNum && c.LoginID == user.LogID
                             select c).First();
            db.tblLogoutRecord.DeleteOnSubmit(delresult);
            db.SubmitChanges();
        }

        public void AddMaterialStockRecord(StockRecordItem sri)
        {
            var recItem = new tblMaterialStockRecord()
            {
                Direction = (int)sri.StockDir,
                MaterialID = (from c in db.tblMaterial
                              where c.Name == sri.MaterialName
                              select c).First().ID,
                Value = sri.Value,
                RecordTime = sri.RecTime,
                Reason = sri.Reason
            };
            db.tblMaterialStockRecord.InsertOnSubmit(recItem);
            db.SubmitChanges();
        }

        #endregion

        #region IOpertation 成员


        public List<PreproduceObject> LoadPreproduceList()
        {
            var result = from prepro in db.tblPreproduce
                         from fd in db.tblFixedDish
                         where prepro.FixedDishID == fd.ID
                         select new PreproduceObject()
                         {
                             FixedDishName = fd.Name,
                             Value = prepro.Value
                         };
            return result.ToList();
        }

        public void UpdatePreproduceList(List<PreproduceObject> preproList)
        {
            //保存预约成品单
            var result = from prepro in db.tblPreproduce
                         select prepro;
            db.tblPreproduce.DeleteAllOnSubmit(result);
            db.SubmitChanges();

            var insertSource = from prepro in preproList
                               from fd in db.tblFixedDish
                               where fd.Name == prepro.FixedDishName
                               select new tblPreproduce()
                               {
                                   FixedDishID = fd.ID,
                                   Value = prepro.Value
                               };
            db.tblPreproduce.InsertAllOnSubmit(insertSource);
            db.SubmitChanges();

            //计入库存
            DateTime time = DateTime.Now;
            foreach (var t in preproList)
            {
                var matsOfFd = from mat in db.tblMaterial
                               from matOffd in db.tblMaterialOfFixedDish
                               from fd in db.tblFixedDish
                               where fd.Name == t.FixedDishName && matOffd.FixedDishID == fd.ID && matOffd.MaterialID==mat.ID
                               select new { matID = mat.ID, matName = mat.Name, usage = matOffd.Dosage };
                foreach (var tmpMatOfFd in matsOfFd)
                {
                    StockRecordItem tmpStockRecItem = new StockRecordItem()
                    {
                        MaterialName = tmpMatOfFd.matName,
                        MaterialID = tmpMatOfFd.matID,
                        StockDir = StockDirection.TakeOut,
                        RecTime = time,
                        Value = tmpMatOfFd.usage * t.Value,
                        Reason = t.FixedDishName + " 预约成品" + t.Value.ToString() + "份原料用料"
                    };
                    AddRec(tmpStockRecItem);
                }
            }
        }

        #endregion

        public bool CheckMoneyVerify(string staffJobNum, string pwd)
        {
            var staffresult = (from staff in db.tblStaff
                               from gro in db.tblAuthorityGroup
                               where staff.JobNumber == staffJobNum && staff.LoginPWD == pwd && staff.GroupID == gro.ID && gro.MoneyManagePermission == true
                               select staff).Count();
            return (staffresult > 0);
        }

        public AuthorityGroup LoadStaffGroup(string staffJobNum, string pwd)
        {
            var result = from staff in db.tblStaff
                         from gro in db.tblAuthorityGroup
                         where staff.JobNumber == staffJobNum && staff.LoginPWD == pwd && staff.GroupID == gro.ID
                         select new AuthorityGroup(gro.Name)
                         {
                             CashierPermission = gro.CashierPermission,
                             DataManagePermission = gro.DataManagePermission,
                             MemberManagePermission = gro.MemberManagePermission,
                             MoneyManagePermission = gro.MoneyManagePermission,
                             ReliefPermission = gro.ReliefPermission,
                             StaffManagePermission = gro.StaffManagePermission,
                             StockManagePermission = gro.StockManagePermission,
                             CashFlowPermission=gro.CashFlowPermission,
                             CheckStockPermission=gro.CheckStockPermission,
                             PurchasePermission=gro.PurchasePermission,
                             ReimbursementPermission=gro.ReimbursementPermission
                         };
            if (result.Count() > 0)
            {
                return result.First();
            }
            return null;
        }

        public SystemUser CheckDutyChange(string staffJobNum, string pwd)
        {
            //var result = from staff in db.tblStaff
            //             from gro in db.tblAuthorityGroup
            //             where staff.JobNumber == staffJobNum && staff.LoginPWD == pwd && staff.GroupID == gro.ID
            //             select new SystemUser()
            //             {
            //                 Group = new AuthorityGroup(gro.Name)
            //                 {
            //                      CashierPermission = gro.CashierPermission,
            //                       DataManagePermission = gro.DataManagePermission,
            //                        MemberManagePermission = gro.MemberManagePermission,
            //                         MoneyManagePermission = gro.MoneyManagePermission,
            //                          ReliefPermission = gro.ReliefPermission,
            //                           StaffManagePermission = gro.StaffManagePermission,
            //                            StockManagePermission = gro.StockManagePermission
            //                 },
            //                  LogID = Login(staffJobNum, pwd),

            //             };
            //if (result.Count() > 0)
            //{
            //    return new SystemUser()
            //    {
                     
            //    };
            //}
            return new SystemUser();
        }

        public int AddKitchenDisplay(int billid, string display, string waitingCard)
        {
            tblKitchen kitchen = new tblKitchen()
            {
                BillID = billid,
                DisplayName = display,
                WaitingCard = waitingCard
            };
            db.tblKitchen.InsertOnSubmit(kitchen);
            db.SubmitChanges();
            return kitchen.ID;
        }

        public List<string> LoadKitchenDisplay()
        {
            List<string> result = (from c in db.tblKitchen
                                   select (c.BillID.ToString() + ":" + c.DisplayName)).ToList();
            return result;
        }

        public void DeleteKitchenDisplay(int billid)
        {
            var result = from c in db.tblKitchen
                         where c.BillID == billid
                         select c;
            db.tblKitchen.DeleteAllOnSubmit(result);
            db.SubmitChanges();
        }


        #region IOpertation 成员


        string IOpertation.LoadKitchenDisplay()
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IOpertation 成员


        public int AddKitchenBill(int billid, string dishes, string waitingCard, string eatPlace)
        {
            tblKitchen kitchen = new tblKitchen()
            {
                BillID = billid,
                DisplayName = dishes,
                WaitingCard = waitingCard,
                EatPlace = eatPlace
            };
            db.tblKitchen.InsertOnSubmit(kitchen);
            db.SubmitChanges();
            return kitchen.ID;
        }

        public int AddKitchenBillItem(int billid, string dishName)
        {
            tblKitchenItem kitItem = new tblKitchenItem()
            {
                BillID = billid,
                DishName = dishName
            };
            db.tblKitchenItem.InsertOnSubmit(kitItem);
            db.SubmitChanges();
            return kitItem.ID;
        }

        public void DeleteKitchenBill(int id)
        {
            var result = from c in db.tblKitchen
                         where c.ID == id
                         select c;
            db.tblKitchen.DeleteAllOnSubmit(result);
            db.SubmitChanges();
        }

        public void DeleteKitchenBillItem(int itemID)
        {
            var result = from c in db.tblKitchenItem
                         where c.ID == itemID
                         select c;
            db.tblKitchenItem.DeleteAllOnSubmit(result);
            db.SubmitChanges();
        }

        public List<string> LoadKitchenBill()
        {
            var result = from c in db.tblKitchen
                         //select c.ID.ToString() + "^" + "账单号：" + c.BillID.ToString() + "\n餐牌：" + c.WaitingCard + "\n送餐地：" + c.EatPlace + "\n--------------------------------\n" + c.DisplayName + "\n";
                         select c.ID.ToString() + "^" + c.DisplayName + "\n";
            return result.ToList();
        }

        public List<string> LoadKitchenBillItem()
        {
            var result = from c in db.tblKitchenItem
                         //select c.ID.ToString() + "$" + c.BillID.ToString() + "：" + c.DishName;
                         select c.ID.ToString() + "$" + c.DishName;
            return result.ToList();
        }

        #endregion

        #region IOpertation 成员


        public void AddMoneyRecord(int logID, string takerJobNum, bool isTake, decimal value)
        {
            tblMoneyRecord money = new tblMoneyRecord()
            {
                Direction = isTake,
                LogID = logID,
                TakerID = (from c in db.tblStaff
                           where c.JobNumber == takerJobNum
                           select c).First().ID,
                Time = DateTime.Now,
                Value = value
            };

            db.tblMoneyRecord.InsertOnSubmit(money);

             //更新给取款时两个账户的余额
            var reslut1 = (from log in db.tblLogRecord
                          from staff in db.tblStaff
                          where log.ID == logID && log.StaffID == staff.ID
                          select staff).First();
            var reslut2 = (from staff in db.tblStaff
                           where staff.JobNumber.Trim().Equals(takerJobNum)
                           select staff).First();

            if (isTake)//管理员向收银员收取金额
            {
                reslut1.Money -= value;
                reslut2.Money += value;
            }
            else//管理员给收银员金额
            {
                reslut1.Money += value;
                reslut2.Money -= value;
            }

            db.SubmitChanges();
        }

        #endregion

        public decimal LoadDiscount(int logID)
        {
            var result = (from c in db.tblBill
                          where c.LogID == logID
                          select c.Discount);
            if (result.Count() > 0)
            {
                return result.Sum();
            }
            else
            {
                return 0.0M;
            }
        }

        public decimal LoadGivenMoney(int logID)
        {
            var result = (from c in db.tblMoneyRecord
                          where c.LogID == logID && c.Direction == false
                          select c.Value);
            if (result.Count() > 0)
            {
                return result.Sum();
            }
            else
            {
                return 0.0M;
            }
        }

        public decimal LoadTakenMoney(int logID)
        {
            var result = (from c in db.tblMoneyRecord
                          where c.LogID == logID && c.Direction == true
                          select c.Value);
            if (result.Count() > 0)
            {
                return result.Sum();
            }
            else
            {
                return 0.0M;
            }
        }

        public List<HistoryBill> LoadHistoryBills(string cashierIP)
        {
            DateTime nowdate = DateTime.Now.Date;
            List<HistoryBill> tmpList = new List<HistoryBill>();
            var billresult = from bill in db.tblBill
                             from staff in db.tblStaff
                             from login in db.tblLogRecord
                             where bill.CashierIP == cashierIP && bill.BillTime.Date == nowdate && bill.LogID == login.ID && login.StaffID == staff.ID
                             select new
                             {
                                 bill, staff
                             };
            foreach (var t in billresult)
            {
                HistoryBill tmpBill = new HistoryBill()
                {
                    BillID = t.bill.ID,
                    BillTime = t.bill.BillTime,
                    CashierJobNum = t.staff.JobNumber,
                    Discount = t.bill.Discount,
                    EatPlace = t.bill.EatPlace,
                    Items = new List<HistoryBill.HistoryBillItem>()
                };
                var billitemresult = from item in db.tblBillItem
                                     where item.BillID == tmpBill.BillID
                                     select item;
                foreach (var a in billitemresult)
                {
                    HistoryBill.HistoryBillItem tmpbillitem = new HistoryBill.HistoryBillItem();
                    tmpbillitem.DishPrice = a.Price;
                    DishType type = (DishType)a.ItemType;
                    switch (type)
                    {
                        case DishType.Combo:
                            {string dishname = string.Empty;
                                var tmpresult = (from c in db.tblComboBill
                                                from d in db.tblCombo
                                                where c.BillItemID == a.ID && c.ComboID == d.ID
                                                select d.Name).First();
                                dishname = tmpresult+"(";

                                var comboresult = from c in db.tblComboBill
                                                  from d in db.tblComboFixedDishBill
                                                  from e in db.tblCombo
                                                  from f in db.tblFixedDish
                                                  where c.BillItemID == a.ID && c.ComboID == e.ID &&
                                                   d.ComboBillID == c.ID && d.FixedDishID == f.ID
                                                  select new
                                                  {
                                                      comboname = e.Name,
                                                      fdname = f.Name
                                                  };
                                foreach (var b in comboresult)
                                {
                                    dishname += b.fdname+"，";
                                }
                                if (comboresult.Count() > 0)
                                {
                                    dishname = dishname.Remove(dishname.Length - 1);
                                }
                                dishname += ")";
                                tmpbillitem.DishName = dishname;
                                tmpBill.Items.Add(tmpbillitem);
                                break;
                            }
                        case DishType.FixedDish:
                            {
                                var fdresult = (from c in db.tblFixedDishBill
                                               from d in db.tblFixedDish
                                               where c.BillItemID == a.ID && c.FixedDishID == d.ID
                                               select new { d.Name }).First();
                                tmpbillitem.DishName = fdresult.Name;
                                tmpBill.Items.Add(tmpbillitem);
                                break;
                            }
                        case DishType.Replaceable:
                            {
                                string dishname = string.Empty;
                                var tmpresult = (from c in db.tblReplaceableDishBill
                                                 from d in db.tblReplaceableDish
                                                 where c.BillItemID == a.ID && c.ReplaceableDishID == d.ID
                                                 select d.Name).First();
                                dishname = tmpresult+"(";

                                var rdresult = from c in db.tblReplaceableDishBill
                                               from d in db.tblReplaceableDish
                                               from e in db.tblReplaceableDishOptionBill
                                               from f in db.tblOption
                                               where c.BillItemID == a.ID && e.ReplaceableDishBillID == c.ID
                                               && c.ReplaceableDishID == d.ID && e.OptionID == f.ID
                                               select new
                                               {
                                                   rdname = d.Name,
                                                   optname = f.Name
                                               };
                                foreach (var b in rdresult)
                                {
                                    dishname += b.optname+"，";
                                }
                                if (rdresult.Count() > 0)
                                {
                                    dishname = dishname.Remove(dishname.Length - 1);
                                }
                                dishname += ")";
                                tmpbillitem.DishName = dishname;
                                tmpBill.Items.Add(tmpbillitem);
                                break;
                            }
                        default: break;
                    }
                }


                tmpList.Add(tmpBill);
            }
            return tmpList;
        }

        public bool CheckUser(string staffJobNum, string pwd)
        {
            int result = (from c in db.tblStaff
                          where c.JobNumber == staffJobNum && c.LoginPWD == pwd
                          select c).Count();
            if (result > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        public int CheckLastLogout(string staffJobNum, string logip)
        {
            var result = (from c in db.tblLogoutRecord
                          where c.StaffJobNum == staffJobNum && c.CashierIP == logip
                          select c.LoginID);
            if (result.Count() > 0)
            {
                return result.First();
            }
            else
            {
                return -1;
            }
        }

        public SystemUser LoadLogRecord(int loginID)
        {
            var result = (from c in db.tblLogRecord
                          from d in db.tblAuthorityGroup
                          from e in db.tblStaff
                          where c.ID == loginID && c.StaffID == e.ID && e.GroupID == d.ID
                          select new SystemUser()
                          {
                              Group = new AuthorityGroup(d.Name)
                              {
                                  CashierPermission = d.CashierPermission,
                                  DataManagePermission = d.DataManagePermission,
                                  MemberManagePermission = d.MemberManagePermission,
                                  MoneyManagePermission = d.MoneyManagePermission,
                                  ReliefPermission = d.ReliefPermission,
                                  StaffManagePermission = d.StaffManagePermission,
                                  StockManagePermission = d.StockManagePermission,
                                  CashFlowPermission=d.CashFlowPermission,
                                  CheckStockPermission=d.CheckStockPermission,
                                  PurchasePermission=d.PurchasePermission,
                                  ReimbursementPermission=d.ReimbursementPermission
                              },
                              LogID = c.ID,
                              StaffID = e.ID,
                              StaffJobNum = e.JobNumber,
                              StaffName = e.Name
                          });
            return result.First();
        }

        public void DeleteLogoutRecord(string staffJobNum, string logip)
        {
            var result = (from c in db.tblLogoutRecord
                          where c.StaffJobNum == staffJobNum && c.CashierIP == logip
                          select c).First();
            db.tblLogoutRecord.DeleteOnSubmit(result);
            db.SubmitChanges();
        }

        #region IOpertation 成员


        public void AddMemberType(string typeName)
        {
            tblMemberType tmpMT = new tblMemberType() { MemberTypeName = typeName };
            db.tblMemberType.InsertOnSubmit(tmpMT);
            db.SubmitChanges();
        }

        public void RenameMemberType(string exName, string newName)
        {
            var result = (from c in db.tblMemberType
                          where c.MemberTypeName == exName
                          select c).First();
            result.MemberTypeName = newName;
            db.SubmitChanges();
        }

        public List<string> LoadMemberType()
        {
            var result = from c in db.tblMemberType
                         select c.MemberTypeName;
            return result.ToList();
        }
        #endregion

        #region IOpertation 成员


        public void AddMember(string membername, string phonenumber, string sex, string birthday, string cardnumber, int membertype, string address, int score, float money, float giftmoney, float sumconsume)
        {
            throw new NotImplementedException();
        }

        public void EditMember(string membername, string phonenumber, string sex, string birthday, string cardnumber, int membertype, string address, int score, float money, float giftmoney, float sumconsume)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region IOpertation 成员


        public List<string> LoadGroupNames()
        {
            var grouplist = this.LoadAuthorityGroups();
            List<string> nameList = new List<string>();
            foreach (var t in grouplist)
            {
                nameList.Add(t.GroupName);
            }
            return nameList;
        }

        #endregion


        #region IOpertation 成员


        public List<string> LoadStaffNames(string groupName)
        {
            var stafflist = this.LoadStaffs(groupName);
            List<string> namelist = new List<string>();
            foreach (var t in stafflist) {
                namelist.Add(t.Name+'('+t.JobNumber+')');
            }

            return namelist;
        }

        #endregion

        #region IOpertation 成员


        public List<BillInfo> loadBillbyStaff(string jobNumber, DateTime today)
        {
            List<BillInfo> billList = new List<BillInfo>();
            
           var result = from staff in db.tblStaff
                         from log in db.tblLogRecord
                         from bill in db.tblBill
                         where staff.JobNumber == jobNumber && log.StaffID == staff.ID
                                && bill.LogID == log.ID && bill.BillTime.Date.Equals(today)
                         select bill;
           foreach (var t in result) {
               BillInfo billInfo = new BillInfo();
               billInfo.BillTime = t.BillTime;
               billInfo.Discount = t.Discount;
               billInfo.BillPirce = getBillPrice(t.ID);
               billInfo.CashierIP = t.CashierIP;
               billInfo.EatPlace = t.EatPlace;
               billList.Add(billInfo);
           }
           return billList;
        }

        public List<BillInfoItem> loadBillItembyStaff(string BillID)
        {
            throw new NotImplementedException();
        }

        #endregion


        #region IOpertation 成员


        public List<int> loadBillIDbyStaff(string jobNumber, DateTime today)
        {
            var result = from staff in db.tblStaff
                         from log in db.tblLogRecord
                         from bill in db.tblBill
                         where staff.JobNumber == jobNumber && log.StaffID == staff.ID
                                && bill.LogID == log.ID && bill.BillTime.Date.Equals(today)
                         select bill.ID;
            return result.ToList();
        }

        #endregion

        #region IOpertation 成员


        public List<MoneyRecordInfo> LoadMoneyRecordbyDate(DateTime today)
        {
            var result = from monRecord in db.tblMoneyRecord
                         where monRecord.Time.Date.Equals(today)
                         select monRecord;
            List<MoneyRecordInfo> moneyRecordList = new List<MoneyRecordInfo>();
            foreach (var i in result) {
                MoneyRecordInfo m = new MoneyRecordInfo();
                m.Money = i.Value;
                m.GiveGetTime = i.Time;
                m.CashierName = LoadCashierName(i.LogID);
                m.AdminName = LoadCashierName(i.TakerID);
                if (i.Direction == false)
                {
                    m.GiveorGet = "←";
                }
                else {
                    m.GiveorGet = "→";
                }

                moneyRecordList.Add(m);
            }

            return moneyRecordList;           
        }
        //获取人员的名字和工卡
        private string LoadCashierName(int id) {
            string name = null;
            var result = from staff in db.tblStaff
                         from log in db.tblLogRecord
                         where log.ID == id && log.StaffID == staff.ID
                         select staff;
            name = result.First().Name + '(' + result.First().JobNumber + ')';
            return name;
        }

        //获取人员的工卡
        private string LoadCashierJobNumber(int id)
        {
            var result = from staff in db.tblStaff
                         from log in db.tblLogRecord
                         where log.ID == id && log.StaffID == staff.ID
                         select staff;
            return result.First().JobNumber;
        }

        //获取今日备用金(false)或上交金额(false)
        private decimal getSpareorHandinSum(int logid,DateTime today,Boolean dir) {
            decimal sum;
            var result = (from money in db.tblMoneyRecord
                           where money.LogID == logid && money.Time.Date.Equals(today)
                           && money.Direction == dir
                           select money.Value);
            if (result.Count() == 0)
            {
                sum = 0.0m;
            }
            else {
                sum = result.Sum();
            }
            return sum;

        }
        //获取人员今日的总收入
        private decimal getComingSum(DateTime today, int logid) {
            var result = from bill in db.tblBill
                         where bill.LogID == logid && bill.BillTime.Date.Equals(today)
                         select bill.ID;
            decimal sum = 0.0m;
            foreach (var i in result) {
                sum += LoadBillPrice(i);
            }
            return sum;
        }
        #endregion

        #region IOpertation 成员


        public List<CashierCashInfo> LoadCashierCash(DateTime today)
        {
            List<CashierCashInfo> CashierCashList = new List<CashierCashInfo>();
            var result = (from bill in db.tblBill
                         where bill.BillTime.Date.Equals(today)
                          select bill.LogID).Distinct();
            
            foreach(var i in result){

                CashierCashInfo c=new CashierCashInfo();
                c.StaffName = LoadCashierName(i);
                c.StaffJobNumber = LoadCashierJobNumber(i);
                c.ComingSum = getComingSum(today, i);
                c.SpareSum = getSpareorHandinSum(i, today, false);
                c.HandinSum = getSpareorHandinSum(i, today, true);
                c.RemainSum = c.ComingSum + c.SpareSum - c.HandinSum;
                CashierCashList.Add(c);
            }

            var res = (from log in db.tblLogRecord
                       from staff in db.tblStaff
                       from bill in db.tblBill
                       where bill.BillTime.Date.Equals(today) && bill.LogID == log.ID
                       && log.StaffID == staff.ID
                       select staff.JobNumber).Distinct();
            List<CashierCashInfo> cc = new List<CashierCashInfo>();
            foreach (string s in res) {
                string name=null;
                decimal cSum = 0.0m, sSum = 0.0m, hSum = 0.0m;
                foreach (CashierCashInfo c in CashierCashList) {
                    if (c.StaffJobNumber.Equals(s)) {
                        cSum += c.ComingSum;
                        sSum += c.SpareSum;
                        hSum += c.HandinSum;
                        name = c.StaffName;
                    }
                }
                CashierCashInfo cci=new CashierCashInfo();
                cci.StaffName=name;
                cci.ComingSum=cSum;
                cci.SpareSum=sSum;
                cci.HandinSum=hSum;
                cci.RemainSum = cci.ComingSum + cci.SpareSum - cci.HandinSum;
                cc.Add(cci);
            }
            return cc;
        }

        #endregion

        #region IOpertation 成员

        //获取本单的原价
        public decimal getBillPrice(int billid)
        {
            var prices = (from c in db.tblBillItem
                          where c.BillID == billid
                          select c.Price);
            if (prices.Count() == 0)
            {
                return 0.0m;
            }
            return prices.Sum();

        }

        #endregion

        #region IOpertation 成员


        public void AddCashierMenoy(int logid, decimal menoy)
        {
            var result =( from log in db.tblLogRecord
                         from staff in db.tblStaff
                         where log.ID == logid && staff.ID == log.StaffID
                         select staff).First();
            result.Money += menoy;
            db.SubmitChanges();
                         
        }

        #endregion

        #region IOpertation 成员


        public void UpdateStaffMenoy(string jobNumber, decimal money)
        {
            var result = (from staff in db.tblStaff
                          where staff.JobNumber.Equals(jobNumber.Trim())
                          select staff).First();
            result.Money = money;
            db.SubmitChanges();
        }

        #endregion

        #region IOpertation 成员


        public void AddReimbursement(string jobNumber, DateTime date, string project, decimal price, int number, decimal Sum, string reason, int state)
        {
            tblReimtursement reim = new tblReimtursement()
            {
                JobNumber = jobNumber,
                Date = date,
                Project = project,
                Price = price,
                Number = number,
                sum = Sum,
                Reason = reason,
                State = state
            };
            db.tblReimtursement.InsertOnSubmit(reim);
            db.SubmitChanges();
        }

        public List<Reimbursementinfo> LoadReimRecord(DateTime date, string jobNumber, bool isAll)
        {
            List<Reimbursementinfo> reimList = new List<Reimbursementinfo>();
            if (isAll)//查看所有报销
            {
                var result = from reim in db.tblReimtursement
                             where reim.JobNumber.Equals(jobNumber)
                             select reim;
                foreach (var i in result) { 
                    Reimbursementinfo reimInfo=new Reimbursementinfo();
                    reimInfo.JobNumber=i.JobNumber;
                    reimInfo.Date = i.Date;
                    reimInfo.Number=i.Number;
                    reimInfo.Price=i.Price;
                    reimInfo.Project=i.Project;
                    reimInfo.Reason=i.Reason;
                    reimInfo.State=i.State;
                    reimInfo.Sum=i.sum;
                    reimInfo.ID = i.ID;
                    reimList.Add(reimInfo);
                }
            }
            else { //查看当天报销
                var result = from reim in db.tblReimtursement
                             where reim.JobNumber.Equals(jobNumber) && reim.Date.Date.Equals(date)
                             select reim;
                foreach (var i in result)
                {
                    Reimbursementinfo reimInfo = new Reimbursementinfo();
                    reimInfo.JobNumber = i.JobNumber;
                    reimInfo.Date = i.Date;
                    reimInfo.Number = i.Number;
                    reimInfo.Price = i.Price;
                    reimInfo.Project = i.Project;
                    reimInfo.Reason = i.Reason;
                    reimInfo.State = i.State;
                    reimInfo.Sum = i.sum;
                    reimInfo.ID = i.ID;
                    reimList.Add(reimInfo);
                }
            }

            return reimList;
        }

        #endregion

        #region IOpertation 成员


        public List<Reimbursementinfo> LoadStaffReimRecord(DateTime date, int state, bool isAll,string type)
        {
            List<Reimbursementinfo> reimList = new List<Reimbursementinfo>();
            if (isAll)//查看所有报销
            {
                if (state == -1)//全部
                {
                    if (type.Equals("全部"))
                    {
                        var result = from reim in db.tblReimtursement
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else if (type.Equals("采购员"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.Reason.Equals("采购员报销")
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else {
                        var result = from reim in db.tblReimtursement
                                     where !reim.Reason.Equals("采购员报销")
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                }
                else { //申请报销或已审批
                   

                    if (type.Equals("全部"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.State == state
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else if (type.Equals("采购员"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.Reason.Equals("采购员报销") && reim.State == state
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else
                    {
                        var result = from reim in db.tblReimtursement
                                     where !reim.Reason.Equals("采购员报销") && reim.State == state
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                }
            }
            else
            { //查看当天报销
               

                if (state == -1)//全部
                {
                    if (type.Equals("全部"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else if (type.Equals("采购员"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.Reason.Equals("采购员报销") && reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else
                    {
                        var result = from reim in db.tblReimtursement
                                     where !reim.Reason.Equals("采购员报销") && reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                }
                else
                { //申请报销或已审批


                    if (type.Equals("全部"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.State == state && reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else if (type.Equals("采购员"))
                    {
                        var result = from reim in db.tblReimtursement
                                     where reim.Reason.Equals("采购员报销") && reim.State == state && reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                    else
                    {
                        var result = from reim in db.tblReimtursement
                                     where !reim.Reason.Equals("采购员报销") && reim.State == state && reim.Date.Date.Equals(date)
                                     select reim;
                        foreach (var i in result)
                        {
                            Reimbursementinfo reimInfo = new Reimbursementinfo();
                            reimInfo.JobNumber = i.JobNumber;
                            reimInfo.Date = i.Date;
                            reimInfo.Number = i.Number;
                            reimInfo.Price = i.Price;
                            reimInfo.Project = i.Project;
                            reimInfo.Reason = i.Reason;
                            reimInfo.State = i.State;
                            reimInfo.Sum = i.sum;
                            reimInfo.ID = i.ID;
                            reimList.Add(reimInfo);
                        }
                    }
                }
            }

            return reimList;
        }

        public void UpdateStaffReimRecord(int[] id, string adminJobNumber)
        {
            for (int i = 0; i < id.Count(); i++) {
                var result = (from reim in db.tblReimtursement
                              where reim.ID == id[i]
                              select reim).First();


                if (result.State == 0)//保证已经审批过的不会重复审批
                {
                    //提取JobNumber
                    string jobNumber = null;
                    int leftIndex = 0;
                    int rightIndex = 0;
                    string staffName = result.JobNumber;
                    for (int j = 0; j < staffName.Length; j++)
                    {
                        if (staffName[j] == '(') leftIndex = j + 1;
                        if (staffName[j] == ')') rightIndex = j - 1;
                    }
                    jobNumber = staffName.Substring(leftIndex, rightIndex - leftIndex + 1);
                    //更改两个账户的余额
                    var res1 = (from staff in db.tblStaff
                                where staff.JobNumber.Equals(jobNumber)
                                select staff).First();
                    res1.Money += result.sum;

                    var res2 = (from staff in db.tblStaff
                                where staff.JobNumber.Equals(adminJobNumber)
                                select staff).First();
                    res2.Money -= result.sum;
                }

                //修改审批状态
                result.State = 1;//表示已审批
            }
            
            db.SubmitChanges();
        }

        #endregion

        #region IOpertation 成员


        public void AddCheckStockRecord(DateTime date, string name, string unit, float oldStock, float todayStock, float difference,int type,int id)
        {


            tblCheckStock cs = new tblCheckStock()
            {
                Date = date,
                materialName = name,
                Uint = unit,
                OldStock = oldStock,
                TodayStock = todayStock,
                Difference = difference,
                CheckType=type
            };
            db.tblCheckStock.InsertOnSubmit(cs);
            //同时修改原料库存量
            var re = (from mat in db.tblMaterial
                      where mat.ID == id
                      select mat.Stock).First();
            re = todayStock;


            db.SubmitChanges();
        }

        public List<CheckStock> LoadCheckStockRecord(DateTime date, int checkPro)
        {
            List<CheckStock> CheckStockList = new List<CheckStock>();
            if (checkPro == 0)
            {//全部
                var re = from check in db.tblCheckStock
                         where check.Date.Date.Equals(date)
                         select check;
                foreach (var i in re) {
                    CheckStock cs = new CheckStock();
                    cs.CheckType = i.CheckType;
                    cs.Difference = (float)i.Difference;
                    cs.MaterialName = i.materialName;
                    cs.OldStock = (float)i.OldStock;
                    cs.TodayStock = (float)i.TodayStock;
                    cs.Unit = i.Uint;
                    cs.Date = i.Date;
                    CheckStockList.Add(cs);
                }
            }
            else{
                var re = from check in db.tblCheckStock
                         where check.Date.Date.Equals(date) && check.CheckType == checkPro
                         select check;
                foreach (var i in re)
                {
                    CheckStock cs = new CheckStock();
                    cs.CheckType = i.CheckType;
                    cs.Difference = (float)i.Difference;
                    cs.MaterialName = i.materialName;
                    cs.OldStock = (float)i.OldStock;
                    cs.TodayStock = (float)i.TodayStock;
                    cs.Unit = i.Uint;
                    cs.Date = i.Date;
                    CheckStockList.Add(cs);
                }
            }

            return CheckStockList;
        }

        public List<StockRecordItem> LoadMaterialStockRecordsMaterial(string materialName,DateTime date)
        {
            return (from mat in db.tblMaterial
                    from rec in db.tblMaterialStockRecord
                    where mat.Name == materialName && mat.ID == rec.MaterialID && rec.RecordTime.Date.Equals(date)
                    select new StockRecordItem()
                    {
                        MaterialName = mat.Name,
                        RecTime = rec.RecordTime,
                        StockDir = (StockDirection)rec.Direction,
                        Value = rec.Value,
                        Reason = rec.Reason,
                        CheckType = mat.CheckID
                    }).ToList();
        }

        #endregion

        #region IOpertation 成员


        public List<MaterialParent> LoadMaterialParent(int id,int type)
        {
            List<MaterialParent> matList = new List<MaterialParent>();
            if (type == 0)
            {
                var result = from m in db.tblMaterialOfFixedDish
                             where id == m.FixedDishID
                             select new MaterialParent()
                             {
                                 ParentID = m.FixedDishID,
                                 ChildID = m.MaterialID,
                                 Dosage = m.Dosage
                             };
                matList = result.ToList();
            }
            else {
                var result = from m in db.tblMaterialOfOption
                             where id == m.OptionID
                             select new MaterialParent()
                             {
                                 ParentID = m.OptionID,
                                 ChildID = m.MaterialID,
                                 Dosage = m.Dosage
                             };
                matList = result.ToList();
            }

            return matList;
        }

        #endregion

        #region IOpertation 成员


        public void AddPurchaseTable(DateTime date, string name, string unit, float old, float nowWant, float nowPurchase)
        {
            tblPurchaseTable pt = new tblPurchaseTable()
            {
                Date = date,
                MaterialName = name,
                Unit = unit,
                OldStock = old,
                NowWantStock = nowWant,
                NowPurchaseStock = nowPurchase
            };
            db.tblPurchaseTable.InsertOnSubmit(pt);
            db.SubmitChanges();
        }

        #endregion

        #region IOpertation 成员


        public List<Reimbursementinfo> LoadPurchaseRecord(DateTime date, string jobNumber, bool isAll)
        {
            List<Reimbursementinfo> reimList = new List<Reimbursementinfo>();
            
            if (isAll)//查看所有报销
            {
                var result = from reim in db.tblReimtursement
                             where reim.JobNumber.Equals(jobNumber)&& reim.Reason.Equals("采购员报销")
                             select reim;
                foreach (var i in result)
                {
                    Reimbursementinfo reimInfo = new Reimbursementinfo();
                    reimInfo.JobNumber = i.JobNumber;
                    reimInfo.Date = i.Date;
                    reimInfo.Number = i.Number;
                    reimInfo.Price = i.Price;
                    reimInfo.Project = i.Project;
                    reimInfo.Reason = i.Reason;
                    reimInfo.State = i.State;
                    reimInfo.Sum = i.sum;
                    reimInfo.ID = i.ID;
                    reimList.Add(reimInfo);
                }
            }
            else
            { //查看当天报销
                var result = from reim in db.tblReimtursement
                             where reim.JobNumber.Equals(jobNumber) && reim.Date.Date.Equals(date)
                             && reim.Reason.Equals("采购员报销")
                             select reim;
                foreach (var i in result)
                {
                    Reimbursementinfo reimInfo = new Reimbursementinfo();
                    reimInfo.JobNumber = i.JobNumber;
                    reimInfo.Date = i.Date;
                    reimInfo.Number = i.Number;
                    reimInfo.Price = i.Price;
                    reimInfo.Project = i.Project;
                    reimInfo.Reason = i.Reason;
                    reimInfo.State = i.State;
                    reimInfo.Sum = i.sum;
                    reimInfo.ID = i.ID;
                    reimList.Add(reimInfo);
                }
            }

            return reimList;
        }

        #endregion

        #region IOpertation 成员


        public List<ForecastInfo> LoadFixedDishCount(DateTime date)
        {
            List<ForecastInfo> foreList = new List<ForecastInfo>();
            DateTime today = new DateTime();
            var res1 = (from bill in db.tblBill
                          from bitem in db.tblBillItem
                          from fdbill in db.tblFixedDishBill
                          from fdish in db.tblFixedDish
                          where bill.ID == bitem.BillID && bitem.ID == fdbill.BillItemID && fdbill.FixedDishID == fdish.ID
                          && bill.BillTime.Date>=date
                          select fdish);
            var res2 = (from bill in db.tblBill
                        from bitem in db.tblBillItem
                        from cbill in db.tblComboBill
                        from cfdbill in db.tblComboFixedDishBill
                        from fdish in db.tblFixedDish
                        where bill.ID == bitem.BillID && bitem.ID == cbill.BillItemID && cfdbill.FixedDishID == fdish.ID
                        && bill.BillTime.Date >= date
                        && cbill.ID==cfdbill.ComboBillID
                        select fdish);
                         
            var result = res1.Concat(res2);

            foreach (var i in db.tblFixedDish) {
                ForecastInfo foreinfo = new ForecastInfo();
                int count = (from r in result
                          where i.ID == r.ID
                          select r).Count();
                
                foreinfo.ID = i.ID;
                foreinfo.Name = i.Name;
                foreinfo.Count = count;
                foreList.Add(foreinfo);

            }
                     

            return foreList;
        }

        public List<ForecastInfo> LoadOptionCount(DateTime date)
        {
            List<ForecastInfo> foreList = new List<ForecastInfo>();

            var result = from bill in db.tblBill
                         from bitem in db.tblBillItem
                         from rbill in db.tblReplaceableDishBill
                         from op in db.tblOption
                         where bill.ID == bitem.BillID && bitem.ID == rbill.BillItemID && rbill.ReplaceableDishID == op.ID
                         && bill.BillTime.Date >= date
                         select op;
            foreach (var i in db.tblFixedDish)
            {
                ForecastInfo foreinfo = new ForecastInfo();
                int count = (from r in result
                             where i.ID == r.ID
                             select r).Count();

                foreinfo.ID = i.ID;
                foreinfo.Name = i.Name;
                foreinfo.Count = count;
                foreList.Add(foreinfo);

            }


            return foreList;
        }

        #endregion
    }

    //Membership
}
